﻿//======================================================================
// Copyright (c) 苏州同程旅游网络科技有限公司. All rights reserved.
// 所属项目：机票业务处理系统
// 创 建 人：宗波
// 创建日期：2011/2/4 16:12:08
// 用    途：数据库操作助手，简单封装DAAB，提供操作数据库的更加简便的方法。

// 修改人：宋平义
// 修改时间：2011/2/14 14:11 重载ExecuteDataTable方法，新加可传递数据库链接方法。
//====================================================================== 

namespace Common
{
    using System;
    using System.Data;
    using System.Collections;
    using System.Collections.Generic;
    using System.Text;
    using System.Data.SqlClient;

    using TCSmartFramework.DataAccess;
    using Microsoft.Practices.EnterpriseLibrary.Common;

    /// <summary>
    /// 数据库操作助手，提供操作数据库的更加简便的方法
    /// </summary>
    public class DBHelper
    {
        #region 常量
        public const string MAX_UPCOUNT = " set rowcount 20; ";
        /// <summary>
        /// 可写数据库连接名称（TcFly）
        /// </summary>
        public const string DB_WRITE = "DB_WRITE";
        /// <summary>
        /// 只读数据库连接名称（TcFly）
        /// </summary>
        public const string DB_READ = "DB_READ";
        /// <summary>
        /// 17uEBooking
        /// </summary>
        public const string DB_17UEBOOKING = "DB_17UEBOOKING";
        /// <summary>
        /// QQ订单数据库(QQFlight)
        /// </summary>
        public const string DB_QQFlight = "DB_QQFlight";
        /// <summary>
        /// 页面监控库(PageMonitorDB)
        /// </summary>
        public const string DB_PAGEMONITOR = "DB_PageMonitorDB";
        /// <summary>
        /// TCFlyUtility
        /// </summary>
        public const string DB_TCFlyUtility = "DB_TCFlyUtility";
        /// <summary>
        /// TCFlyTTS
        /// </summary>
        public const string DB_TCFlyTTS = "DB_TCFlyTTS";
        /// <summary>
        /// FlightQuery
        /// </summary>
        public const string DB_FlightQuery = "DB_FlightQuery";
        /// <summary>
        /// 17unet
        /// </summary>
        public const string DB_17uNet = "DB_17uNet";
        /// <summary>
        /// db_TcFlyZRule
        /// </summary>
        public const string DB_TcFlyZRule = "DB_TcFlyZRule";
        /// <summary>
        /// DB_TCShare
        /// </summary>
        public const string DB_TCShare = "DB_TCShare";
        /// <summary>
        /// DB_TCFlyReResouce
        /// </summary>
        public const string DB_TCFlyResouce = "DB_TCFlyResouce";
        /// <summary>
        /// DB_DEMO
        /// </summary>
        public const string DB_DEMO = "DB_DEMO";
        public static string DB_WriteOnLine = "DB_WriteOnLine";

        public static string DB_WriteLocalhost = "DB_WriteLocalhost";
        #endregion

        #region 获取Database对象

        /// <summary>
        /// 获取DataBase对象，此对象使用可写的数据库连接。
        /// </summary>
        public static Database GetDB()
        {
            return GetDB(DBHelper.DB_WRITE);
        }

        /// <summary>
        /// 使用指定数据库连接字符串创建Database对象。
        /// </summary>
        /// <param name="connectionName"></param>
        /// <returns></returns>
        public static Database GetDB(string connectionName)
        {
            return DatabaseFactory.GetWriteDatabase(connectionName);
        }

        #endregion

        #region 获取SqlConnection对象

        /// <summary>
        /// 获取SqlConnection对象，未open，使用完毕请close。
        /// </summary>
        /// <returns></returns>
        public static SqlConnection GetSqlConnection()
        {
            Database db = GetDB();
            SqlConnection con = db.CreateConnection() as SqlConnection;
            return con;
        }

        /// <summary>
        /// 获取SqlConnection对象，未open，使用完毕请close。
        /// </summary>
        /// <param name="db_Name">使用的数据库</param>
        /// <returns>SqlConnection</returns>
        public static SqlConnection GetSqlConnection(string db_Name)
        {
            Database db = GetDB(db_Name);
            SqlConnection con = db.CreateConnection() as SqlConnection;
            return con;
        }

        #endregion

        #region 执行插入操作，返回自增长ID

        /// <summary>
        /// 执行插入操作，返回自增长ID
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteIdentity(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql);

            int id = 0;
            Database db = GetDB();
            if (db != null)
            {
                object obj = db.ExecuteScalar(cmd);
                if (obj != null)
                    int.TryParse(obj.ToString(), out id);
            }
            else
            {
                throw new CustomException("获取Database错误，返回null");
            }

            //add by grs at 2011-03-04 for 关闭连接
            try
            {
                cmd.Connection.Close();
            }
            catch { }

            return id;
        }


        /// <summary>
        /// 执行插入操作，返回自增长ID
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramArray"></param>
        /// <returns></returns>
        public static int ExecuteIdentity(string sql, SqlParameter[] paramArray)
        {
            Database db = GetDB();
            SqlConnection con = db.CreateConnection() as SqlConnection;
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            if (paramArray != null && paramArray.Length > 0)
                cmd.Parameters.AddRange(paramArray);
            int id = 0;

            string identity = " select @@IDENTITY as ID ";
            cmd.CommandText = sql + identity;
            object obj = cmd.ExecuteScalar();
            if (obj != null)
                int.TryParse(obj.ToString(), out id);
			//add by grs at 2011-03-04 for 关闭连接
            try
            {
                con.Close();
            }
            catch { }
            return id;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramArray"></param>
        /// <param name="Connectionname"></param>
        /// <returns></returns>
        public static int ExecuteIdentity(string sql, SqlParameter[] paramArray, string Connectionname)
        {
            Database db = GetDB(Connectionname);
            SqlConnection con = db.CreateConnection() as SqlConnection;
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            if (paramArray != null && paramArray.Length > 0)
                cmd.Parameters.AddRange(paramArray);
            int id = 0;

            string identity = " select @@IDENTITY as ID ";
            cmd.CommandText = sql + identity;
            object obj = cmd.ExecuteScalar();
            if (obj != null)
                int.TryParse(obj.ToString(), out id);

            return id;
        }
        #endregion

        #region 执行SQL语句返回DT，如果失败或异常，可能返回null

        /// <summary>
        /// 执行SQL语句返回DT，不需要参数，如果失败或异常，可能返回null
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql);

            Database db = GetDB();
            DataTable dt = null;
            if (db != null)
            {
                DataSet ds = db.ExecuteDataSet(cmd);

                if (ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            else
            {
                throw new CustomException("获取Database错误，返回null");
            }

            //add by grs at 2011-03-04 for 关闭连接
            try
            {
                cmd.Connection.Close();
            }
            catch { }

            return dt;
        }

        /// <summary>
        /// 执行SQL语句返回DT，需要设定读写链接，如果失败或异常，可能返回null
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionName">链接名称</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, string connectionName)
        {
            SqlCommand cmd = new SqlCommand(sql);

            Database db = GetDB(connectionName);
            DataTable dt = null;
            if (db != null)
            {
                DataSet ds = db.ExecuteDataSet(cmd);

                if (ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            else
            {
                throw new CustomException("获取Database错误，返回null");
            }

            //add by grs at 2011-03-04 for 关闭连接
            try
            {
                cmd.Connection.Close();
            }
            catch { }

            return dt;
        }

        /// <summary>
        /// 执行SQL语句返回DT，如果失败或异常，可能返回null
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramArray"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, SqlParameter[] paramArray)
        {
            SqlCommand cmd = new SqlCommand(sql);
            if (paramArray != null && paramArray.Length > 0)
                cmd.Parameters.AddRange(paramArray);

            Database db = GetDB();
            DataTable dt = null;
            if (db != null)
            {
                DataSet ds = db.ExecuteDataSet(cmd);

                if (ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            else
            {
                throw new CustomException("获取Database错误，返回null");
            }

            //add by grs at 2011-03-04 for 关闭连接
            try
            {
                cmd.Connection.Close();
            }
            catch { }

            return dt;
        }
        /// <summary>
        ///  执行SQL语句返回DT，如果失败或异常，可能返回null
        /// </summary>
        /// <param name="sql">执行的sql</param>
        /// <param name="paramArray"></param>
        /// <param name="connectionName">数据库链接名称</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, SqlParameter[] paramArray,string connectionName)
        {
            SqlCommand cmd = new SqlCommand(sql);
            if (paramArray != null && paramArray.Length > 0)
                cmd.Parameters.AddRange(paramArray);

            Database db = GetDB(connectionName);
            DataTable dt = null;
            if (db != null)
            {
                DataSet ds = db.ExecuteDataSet(cmd);

                if (ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            else
            {
                throw new CustomException("获取Database错误，返回null");
            }

            //add by grs at 2011-03-04 for 关闭连接
            try
            {
                cmd.Connection.Close();
            }
            catch { }

            return dt;
        }

        #endregion

        #region 执行SQL语句，返回影响行数

        /// <summary>
        /// 执行SQL语句，返回影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql);

            Database db = GetDB();
            if (db == null)
                throw new CustomException("获取Database错误，返回null");

            int n = 0;
            try
            {
                n = db.ExecuteNonQuery( cmd );
            }
            catch ( Exception ex )
            {
                Logger.WriteLog( "执行SQL语句，返回影响行数异常", ex );
            }
            finally
            {
                //add by grs at 2011-03-04 for 关闭连接
                try
                {
                    cmd.Connection.Close();
                }
                catch { }
            }

            return n;
        }

        /// <summary>
        /// 执行SQL语句，返回影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionName">连接数据库字符串</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, string connectionName)
        {
            SqlCommand cmd = new SqlCommand(sql);

            Database db = GetDB(connectionName);
            if (db == null)
                throw new CustomException("获取Database错误，返回null");

            int n = 0;
            try
            {
                n = db.ExecuteNonQuery( cmd );
            }
            catch ( Exception ex )
            {
                Logger.WriteLog( "执行SQL语句，返回影响行数异常", ex );
            }
            finally
            {
                //add by grs at 2011-03-04 for 关闭连接
                try
                {
                    cmd.Connection.Close();
                }
                catch { }
            }

            return n;
        }
        /// <summary>
        /// 执行SQL语句，返回影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramArray"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, SqlParameter[] paramArray)
        {
            SqlCommand cmd = new SqlCommand(sql);
            if (paramArray != null && paramArray.Length > 0)
            {
                cmd.Parameters.AddRange(paramArray);
            }

            Database db = GetDB();
            if (db == null)
                throw new CustomException("获取Database错误，返回null");

            int n = 0;
            try
            {
                n = db.ExecuteNonQuery( cmd );
            }
            catch ( Exception ex )
            {
                Logger.WriteLog( "执行SQL语句，返回影响行数异常", ex );
            }
            finally
            {
                //add by grs at 2011-03-04 for 关闭连接
                try
                {
                    cmd.Connection.Close();
                }
                catch { }
            }

            return n;
        }

       /// <summary>
        /// 执行SQL语句，返回影响行数
       /// </summary>
       /// <param name="sql"></param>
       /// <param name="paramArray"></param>
       /// <param name="connectionName">连接数据库字符串</param>
       /// <returns></returns>
        public static int ExecuteNonQuery(string sql, SqlParameter[] paramArray, string connectionName)
        {
            SqlCommand cmd = new SqlCommand(sql);
            if (paramArray != null && paramArray.Length > 0)
            {
                cmd.Parameters.AddRange(paramArray);
            }

            Database db = GetDB(connectionName);
            if (db == null)
                throw new CustomException("获取Database错误，返回null");

            int n = 0;
            try
            {
                n = db.ExecuteNonQuery( cmd );
            }
            catch ( Exception ex )
            {
                Logger.WriteLog( "执行SQL语句，返回影响行数异常", ex );
            }
            finally
            {
                //add by grs at 2011-03-04 for 关闭连接
                try
                {
                    cmd.Connection.Close();
                }
                catch { }
            }

            return n;
        }

        #endregion

        #region 事务操作
        
        /// <summary>
        /// 执行插入操作，返回自增长ID
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteIdentity(string sql, SqlTransaction tran)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = tran.Connection;
            cmd.Transaction = tran;
            int id = 0;
            string identity = " select @@IDENTITY as ID ";
            cmd.CommandText = sql + identity;
            object obj = cmd.ExecuteScalar();
            if (obj != null)
                int.TryParse(obj.ToString(), out id);

            return id;
        }

        /// <summary>
        /// 执行插入操作，返回自增长ID
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteIdentity(string sql, SqlParameter[] paramArray, SqlTransaction tran)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = tran.Connection;
            cmd.Transaction = tran;
            cmd.Parameters.AddRange(paramArray);

            int id = 0;
            string identity = " select @@IDENTITY as ID ";
            cmd.CommandText = sql + identity;
            object obj = cmd.ExecuteScalar();
            if (obj != null)
                int.TryParse(obj.ToString(), out id);

            return id;
        }

        /// <summary>
        /// 事务中执行SQL语句，返回影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, SqlTransaction tran)
        {
            SqlCommand command = new SqlCommand(sql, tran.Connection);
            command.Transaction = tran;
            return command.ExecuteNonQuery();
        }

        /// <summary>
        /// 事务中执行SQL语句，返回影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramArray"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, SqlParameter[] paramArray, SqlTransaction tran)
        {
            SqlCommand command = new SqlCommand(sql, tran.Connection);
            command.Transaction = tran;
            command.Parameters.AddRange(paramArray);
            return command.ExecuteNonQuery();
        }

        /// <summary>
        /// 事务中执行SQL语句返回DT
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, SqlTransaction tran)
        {
            SqlCommand cmd = new SqlCommand(sql, tran.Connection);
            cmd.Transaction = tran;
            Database db = GetDB();
            DataTable dt = null;
            if (db != null)
            {
                DataSet ds = db.ExecuteDataSet(cmd);

                if (ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            else
            {
                throw new CustomException("获取Database错误，返回null");
            }

            return dt;
        }

        /// <summary>
        /// 事务中执行SQL语句返回DT
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramArray"></param>
        /// <param name="tran"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, SqlParameter[] paramArray, SqlTransaction tran)
        {
            SqlCommand cmd = new SqlCommand(sql, tran.Connection);
            cmd.Transaction = tran;
            cmd.Parameters.AddRange(paramArray);

            Database db = GetDB();
            DataTable dt = null;
            if (db != null)
            {
                DataSet ds = db.ExecuteDataSet(cmd);

                if (ds.Tables.Count > 0)
                    dt = ds.Tables[0];
            }
            else
            {
                throw new CustomException("获取Database错误，返回null");
            }

            return dt;
        }

        #endregion
    }
}
